

/**
	Author	    : Rohan
	Date	    : August 9th, 2004
	Description : This method gets the list of all the process steps coming in the process flows
		      leading up to the given process step.
	Usage 	    : select * from fn_get_process_flow_of_process_steps( '1206,1209' )
		      select * from fn_get_process_flow_by_processstep_id_new ( 1206 )	
*/


ALTER  function fn_get_process_flow_of_process_steps
( 
@process_steps varchar(2000) -- List of Process Step Ids seperated by comma
)
returns  @fn_result Table( ProcessStep_ID bigint, 
			   PS_Type varchar(20), 
			   Process_Type_Name varchar(20), 
			   Product_name varchar(50), 
			   Product_id INT )
As
begin

--declarations
declare @process_step_id bigint
declare @row_id int
declare @new_ps int
declare @to_be_checked_ps int
declare @checked_ps int
declare @ps_count int
declare @true int
declare @false int
declare @loop_status int
declare @query_string nvarchar(1200)
declare @ps_type varchar(20)
declare	@process_type_name varchar(20)
declare	@product_name varchar(50)
declare @product_id int

declare @result Table(  row_id int IDENTITY,
			ProcessStep_ID bigint, 
			Ps_Checked_Status int,
			PS_Type varchar(20), 
			Process_Type_Name varchar(20), 
			Product_name varchar(50), 
			Product_id int )	

--initializations
set @new_ps = 1
set @to_be_checked_ps = 2
set @checked_ps = 3
set @true = 1
set @false = 0
set @loop_status = @true

--retreiving process step Id's from the given string of process steps
insert into @result( ProcessStep_ID, Ps_Checked_Status )  select ProcessStep_Id, @to_be_checked_ps from fn_get_process_steps_in_string( @process_steps )


--looping to get all the process steps preceeding the process steps present in the @results table
while( @loop_status = @true )
begin
	--selecting the previous process steps from the process step traverse table
	insert into @result( ProcessStep_ID, Ps_Checked_Status ) 
	select distinct ProcessStep_Id, @new_ps from Process_Step_Traverse_table 
	where Next_ProcessStep_ID in ( select ProcessStep_ID from @result where Ps_Checked_Status = @to_be_checked_ps ) 
	
	--delete the process steps which are already existing in the table and have been selected again
	delete from @result where Ps_Checked_Status in ( @to_be_checked_ps, @checked_ps  ) 
			    and ProcessStep_ID in ( select ProcessStep_ID from @result where Ps_Checked_Status = @new_ps )
	
	--changing the statuses 	
	update @result set Ps_Checked_Status = @checked_ps where Ps_Checked_Status = @to_be_checked_ps
	update @result set Ps_Checked_Status = @to_be_checked_ps where Ps_Checked_Status = @new_ps

	select @ps_count = count(*) from @result where Ps_Checked_Status = @to_be_checked_ps
	if( @ps_count = 0 )
	begin
		--exit the loop if there are no more process steps to be check 
		set @loop_status = @false
	end
end

--selecting the the details pertaining to the Process Step ID
declare @resultsCursor CURSOR
set @resultsCursor = cursor for select ProcessStep_Id, Row_Id from @result
open @resultsCursor 
fetch next from @resultsCursor into @process_step_id, @row_id
while( @@fetch_status = 0 )
begin
	select @ps_type = PS_Type, @process_type_name = PS_Type_Name, @product_name = Part_Number, @product_id = Product_id from product, process_step where product.processStep_ID = process_step.processStep_ID and product.processStep_ID = @process_step_id
	update @result set PS_Type = @ps_type,
			   Process_Type_Name = @process_type_name,
			   Product_name = @product_name,
			   Product_id = @product_id where Row_Id = @row_id

	fetch next from @resultsCursor into @process_step_id, @row_id
end
close @resultsCursor
deallocate @resultsCursor
	
insert into @fn_result( ProcessStep_ID, PS_Type, Process_Type_Name, Product_name, Product_id ) select ProcessStep_ID, PS_Type, Process_Type_Name, Product_name, Product_id from @result order by row_id desc

return 
end --End of proc



